﻿using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;

namespace PointTracker
{
    /// &ltsummary>
    /// An static class for managing a connection with and data to and from an instance of SQL Server.
    /// The class must be inherited to override the Formatted method, specific to an individual's needs.
    /// </summary>
    public static class DatabaseManager
    {

        private static string datasource = System.Configuration.ConfigurationManager.ConnectionStrings["pointTrackerConnectionString"].ConnectionString;

        public static Boolean getPassword(string fname, string lname, string email, string newpw)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();

            command.CommandType = System.Data.CommandType.Text;           
            connection.Open();
            

            command.Parameters.AddWithValue("@fname", fname.ToLower());
            command.Parameters.AddWithValue("@lname", lname.ToLower());
            command.Parameters.AddWithValue("@email", email.ToLower());
            command.Parameters.AddWithValue("@pw", newpw.GetHashCode());
            command.CommandText = "UPDATE [user] SET password = @pw WHERE ([user].fname = @fname AND [user].lname = @lname AND [user].email = @email)";
            int value = command.ExecuteNonQuery();
            connection.Close();
            if (value == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public static Boolean addUser(string fname, string lname, string email, int password, int role, int crn)
        {
            Boolean retVal = false;
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.AddWithValue("@fname", fname.ToLower());
            command.Parameters.AddWithValue("@lname", lname.ToLower());
            command.Parameters.AddWithValue("@email", email.ToLower());
            command.Parameters.AddWithValue("@password", password);
            command.Parameters.AddWithValue("@role", role);
            connection.Open();
            int affectedRows = 0;
            if(role == 2){ //Scribe
                command.CommandText = @"SELECT [user].fname, [user].lname, [user].email FROM [user] WHERE 
                [user].fname = @fname AND [user].lname = @lname AND [user].email = @email";

                SqlDataReader reader = command.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    i++;
                }
                reader.Close();
                if (i > 0)
                {
                    command.Parameters.AddWithValue("@crn", crn);
                    command.CommandText = "INSERT INTO [user_class_relation] VALUES (@email, @crn)";
                    affectedRows = affectedRows + command.ExecuteNonQuery();
                }
                else
                {
                    command.CommandText = "INSERT INTO [user] VALUES (@fname, @lname, @email, @password, @role)";
                    affectedRows = command.ExecuteNonQuery();

                    command.Parameters.AddWithValue("@crn", crn);
                    command.CommandText = "INSERT INTO [user_class_relation] VALUES (@email, @crn)";
                    affectedRows = affectedRows + command.ExecuteNonQuery();
                }
            }
            else//Professor or admin
            {
                command.CommandText = "INSERT INTO [user] VALUES (@fname, @lname, @email, @password, @role)";
                try
                {
                    affectedRows = command.ExecuteNonQuery();
                }
                catch
                {
                    return false;
                }
            }          
            
            if (affectedRows >= 1)
            {
                retVal = true;
            }

            connection.Close();
            command.Parameters.Clear();

            return retVal;
        }

        public static Boolean modifyUser(string fname, string lname, string email, string password)
        {
            return true;
        }

        public static Boolean removeUser(string fname, string lname, string email)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "DELETE FROM [user] WHERE [user].fname = @fname AND [user].lname = @lname AND [user].email = @email";
            command.Parameters.AddWithValue("@email", email);
            command.Parameters.AddWithValue("@fname", fname);
            command.Parameters.AddWithValue("@lname", lname);
            connection.Open();
            int succeed = command.ExecuteNonQuery();
            connection.Close();
            command.Parameters.Clear();
            if (succeed == 1)
                return true;
            else return false;
        }

        public static string[] getUser(string email, int password)
        {

            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "SELECT [user].fname, [user].lname, [user].email, [user].role FROM [user] WHERE [user].email = @email AND [user].password = @password";
            command.Parameters.AddWithValue("@email", email);
            command.Parameters.AddWithValue("@password", password);
            string[] user = new string[4];
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            if (!reader.Read())
            {
                for (int i = 0; i < user.Length; i++)
                    user[i] = "";
            }
            else
            {
                user[0] = reader["fname"].ToString();
                user[1] = reader["lname"].ToString();
                user[2] = reader["email"].ToString();
                user[3] = reader["role"].ToString();
            }
            reader.Close();
            connection.Close();
            command.Parameters.Clear();
            return user;
        }

        public static List<List<String>> getUsers(int crn, int role)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;

            command.CommandText = @"SELECT
                [user].fname, [user].lname, [user].email
                FROM [user]
                INNER JOIN
                [user_class_relation] on [user].email = [user_class_relation].email
                WHERE
                [user_class_relation].crn = @crn
                AND
                [user].role = @role";
            command.Parameters.AddWithValue("@crn", crn);
            command.Parameters.AddWithValue("@role", role);

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            List<List<String>> users = new List<List<string>>();
            while (reader.Read())
            {
                List<string> tempUser = new List<string>();
                tempUser.Add(reader["fname"].ToString());
                tempUser.Add(reader["lname"].ToString());
                tempUser.Add(reader["email"].ToString());
                users.Add(tempUser);
            }

            reader.Close();
            connection.Close();
            command.Parameters.Clear();
            return users;
        }

        public static Boolean addClass(int crn, string className, int days, int term, int year, string email)
        {

            Boolean retVal = false;
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;

            command.CommandText = "INSERT INTO [class] VALUES (@crn, @name, @days, @term, @year)";
            command.Parameters.AddWithValue("@crn", crn);
            command.Parameters.AddWithValue("@name", className);
            command.Parameters.AddWithValue("@days", days);
            command.Parameters.AddWithValue("@term", term);
            command.Parameters.AddWithValue("@year", year);
            command.Parameters.AddWithValue("@email", email);
            connection.Open();
            int affectedRows = 0;
            try
            {
                 affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                return false; 
            }
            command.CommandText = "INSERT INTO [user_class_relation] VALUES (@email, @crn)";
            try
            {
                affectedRows = affectedRows + command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                return false;
            }
            if (affectedRows == 2)
            {
                retVal = true;
            }

            connection.Close();
            command.Parameters.Clear();
            return retVal;
        }

        public static Boolean modifyClass(int crn, string className, PointTracker.days days, int term, int year)
        {
            return true;
        }

        public static Boolean removeClass(int crn, int term, int year)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            int val = 0;

            command.CommandText = "DELETE FROM [user_class_relation] WHERE [user_class_relation].crn = @crn";
            command.Parameters.AddWithValue("@crn", crn);
            connection.Open();
            command.ExecuteNonQuery();

            command.CommandText = "DELETE FROM [student] WHERE [student].class_crn = @crn";
            val = command.ExecuteNonQuery();

            command.CommandText = "DELETE FROM [class] WHERE [class].crn = @crn AND [class].term = @term AND [class].year = @year";
            command.Parameters.AddWithValue("@term", term);
            command.Parameters.AddWithValue("@year", year);
            
            if(val > -1)
                val = command.ExecuteNonQuery();
            connection.Close();
            if (val > -1)
                return true;
            else
                return false;
        }

        public static List<List<string>> getClasses(string email)
        {
             SqlConnection connection = new SqlConnection(datasource);
             SqlCommand command = connection.CreateCommand();
             command.CommandType = System.Data.CommandType.Text;
             command.CommandText = @"SELECT
                [class].crn, [class].name, [class].days, [class].term, [class].year
                FROM [class]
                INNER JOIN
                [user_class_relation] ON [class].crn = [user_class_relation].crn
                WHERE
                [user_class_relation].email = @email";
             command.Parameters.AddWithValue("@email", email);

             connection.Open();
             SqlDataReader reader = command.ExecuteReader();
             List<List<string>> classes = new List<List<string>>();
             while (reader.Read())
             {
                 List<string> temp = new List<string>();
                 temp.Add(reader["crn"].ToString());
                 temp.Add(reader["name"].ToString());
                 temp.Add(reader["days"].ToString());
                 temp.Add(reader["term"].ToString());
                 temp.Add( reader["year"].ToString());
                 classes.Add(temp);                                   
             }
             connection.Close();
             reader.Close();
             return classes;
        }

        
        public static Boolean modifyStudent(string fname, string lname, string id, int points, int crn)
        {
            return true;
        }


        public static Boolean addStudents(List<Student> students, int crn)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "INSERT INTO [student] VALUES (@fname, @lname, @du_id, @points, @crn)";
            connection.Open();
            int total = 0;
            foreach (Student student in students){
                command.Parameters.AddWithValue("@fname", student.fname);
                command.Parameters.AddWithValue("@lname", student.lname);
                command.Parameters.AddWithValue("@du_id", student.duId);
                command.Parameters.AddWithValue("@points", student.points);
                command.Parameters.AddWithValue("@crn", crn);
                try
                {
                    total = total + command.ExecuteNonQuery();
                }
                catch
                {
                    return false;
                }
                command.Parameters.Clear();
            }
            connection.Close();            
            if(total == students.Count)
                return true;
            else
                return false;
        }

        public static List<List<String>> getStudents(int crn)
         {
            List<List<String>> students = new List<List<string>>();
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = @"SELECT
                [student].fname, [student].lname, [student].du_id, [student].points
                FROM [student]
                WHERE
                [student].class_crn = @crn";
               command.Parameters.AddWithValue("@crn", crn);
               connection.Open();
               SqlDataReader reader = command.ExecuteReader();
           while(reader.Read())
            {
                List<string> tempStudent = new List<string>();
                tempStudent.Add(reader["fname"].ToString());
                tempStudent.Add(reader["lname"].ToString());
                tempStudent.Add(reader["du_id"].ToString());
                tempStudent.Add(reader["points"].ToString());
                students.Add(tempStudent);
            }
           command.Parameters.Clear();
           connection.Close();
           return students;
         }

        public static Boolean deleteStudent(string fname, string lname, string du_id)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "DELETE FROM [student] WHERE [student].fname = @fname AND [student].lname = @lname AND [student].du_id = @du_id";
            command.Parameters.AddWithValue("@du_id", du_id);
            command.Parameters.AddWithValue("@fname", fname);
            command.Parameters.AddWithValue("@lname", lname);
            connection.Open();
            int succeed = 0;
            try
            {
                succeed = command.ExecuteNonQuery();
            }
            catch {
                return false;
            }
            connection.Close();
            command.Parameters.Clear();
            if (succeed == 1)
                return true;
            else return false;
        }

        public static Boolean updatePoints(List<Student> students, int class_crn)
        {
            SqlConnection connection = new SqlConnection(datasource);
            SqlCommand command = connection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "UPDATE [student] SET points = @points WHERE ([student].class_crn = @crn AND [student].du_id = @duid)";
            connection.Open();
            int succeed = 0;
            foreach (Student student in students)
            {
                command.Parameters.AddWithValue("@points", student.points);
                command.Parameters.AddWithValue("@duid", student.duId);
                command.Parameters.AddWithValue("@crn", class_crn);
                succeed = succeed + command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            connection.Close();
            if (succeed == students.Count)
                return true;
            else
                return false;
        }
        
    }
}
